package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.joda.time.DateTime;

import com.google.gson.Gson;

import data.Athlete;
import data.Competition;
import data.Event;
import data.Relay;
import data.Result;
import data.Team;

public class Splash_databaseController extends DataController {

	public Splash_databaseController(Connection connection) {
		super(connection);
	}

	@Override
	public String loadCompetition() throws SQLException {

		String json = "";
		Statement statement = connection.createStatement();

		DateTime startDate = null;
		DateTime endDate = new DateTime();

		ResultSet rs1 = statement.executeQuery("SELECT STARTDATE FROM SWIMSESSION ORDER BY STARTDATE");
		while(rs1.next())
		{
			if (startDate == null) {
				startDate = new DateTime(rs1.getLong("STARTDATE"));
			}

			endDate = new DateTime(rs1.getLong("STARTDATE"));
		}

		if (startDate == null) {
			startDate = new DateTime();
		}

		ResultSet rs = statement.executeQuery("SELECT DATA FROM BSGLOBAL WHERE NAME = 'MEETVALUES'");
		while(rs.next())
		{
			String [] data = rs.getString("DATA").split("\n");

			Competition competition = new Competition(
					getDataItem(data, "name"), 
					getDataItem(data, "city"), 
					getDataItem(data, "poolname"), 
					startDate.toString("yyyy-MM-dd"),  
					endDate.toString("yyyy-MM-dd"),  
					getDataItem(data, "course").equals("1") ? 50 : 25
					);
			json = new Gson().toJson(competition);
		}

		return json;
	}

	private String getDataItem(String [] data, String key) {

		String result = "";

		try {
			for (String row : data) {
				if (row.split("=")[0].toLowerCase().equals(key)) {
					String [] tokens = row.split(";");
					result = tokens[1];
					break;
				}
			}
		} catch (Exception e) {
			System.err.println("Couldn't parse data: " + data);
		}

		return result.trim();

	}

	@Override
	public List<Event> loadEvents() throws SQLException {
		List<Event> events = new ArrayList<Event>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT SWIMEVENT.SWIMEVENTID as event_id, EVENTNUMBER, DISTANCE * RELAYCOUNT as distance, CASE STROKE WHEN 1 then 'free' WHEN 2 THEN 'back' WHEN 3 THEN 'breast' WHEN 4 THEN 'fly' WHEN 5 THEN 'medley' END as stroke, CASE RELAYCOUNT WHEN 1 THEN 0 ELSE 1 END as relay, CASE AGEMIN WHEN -1 THEN 0 ELSE AGEMIN END as low_age, CASE AGEMAX WHEN -1 THEN 109 ELSE AGEMAX END as high_age, CASE SWIMEVENT.GENDER WHEN 1 THEN 'M' WHEN 2 THEN 'F' END as gender, CASE ROUND WHEN 1 THEN 'F' WHEN 9 THEN 'F' WHEN 2 THEN 'P' WHEN 3 THEN 'SO' END as type, STARTDATE as date FROM SWIMEVENT, SWIMSESSION, SWIMSTYLE, AGEGROUP WHERE SWIMSESSION.SWIMSESSIONID = SWIMEVENT.SWIMSESSIONID AND SWIMEVENT.SWIMSTYLEID = SWIMSTYLE.SWIMSTYLEID AND SWIMEVENT.SWIMEVENTID = AGEGROUP.SWIMEVENTID ORDER BY SWIMEVENT.EVENTNUMBER");

		while(rs.next())
		{
			events.add(new Event(
					rs.getInt("event_id"),
					rs.getInt("EVENTNUMBER"),
					rs.getInt("distance"),
					rs.getString("stroke"), 
					rs.getInt("relay"),
					rs.getInt("low_age"),
					rs.getInt("high_age"),
					rs.getString("gender"), 
					rs.getString("type"), 
					new DateTime(rs.getLong("date")).toString("yyyy-MM-dd")
					));
		}

		return events;
	}

	@Override
	public String loadAthletes() throws SQLException {
		List<Athlete> athletes = new ArrayList<Athlete>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT ATHLETEID as ath_no, CLUB.NAME as team, LASTNAME, FIRSTNAME, BIRTHDATE, CASE GENDER WHEN 2 THEN 'F' ELSE 'M' END as gender FROM ATHLETE, CLUB WHERE ATHLETE.CLUBID = CLUB.CLUBID");
		while(rs.next())
		{
			athletes.add(new Athlete(
					rs.getInt("ath_no"),
					rs.getString("FIRSTNAME"), 
					rs.getString("LASTNAME"), 
					rs.getString("team"), 
					new DateTime(rs.getLong("BIRTHDATE")).toString("yyyy-MM-dd"),
					rs.getString("gender")
					));
			
		}
		return new Gson().toJson(athletes);
	}

	@Override
	public String loadTeams() throws SQLException {

		List<Team> teams = new ArrayList<Team>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT CLUBID, NAME, SHORTNAME, CODE from CLUB");
		while(rs.next())
		{
			teams.add(new Team(
					rs.getInt("CLUBID"),
					rs.getString("NAME"), 
					rs.getString("SHORTNAME") == null ? rs.getString("CODE") : rs.getString("SHORTNAME"), 
					rs.getString("CODE")
					));
		}

		return new Gson().toJson(teams);
	}

	@Override
	public List<Result> loadResults() throws SQLException {
		List<Result> results = new ArrayList<Result>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT SWIMRESULTID, SWIMEVENT.SWIMEVENTID as event_id, CASE ROUND WHEN 1 THEN 'F' WHEN 9 THEN 'F' WHEN 2 THEN 'P' WHEN 3 THEN 'SO' END as type, SWIMRESULT.ATHLETEID as ath_no, CLUB.CLUBID as team_no, CASE RESULTSTATUS WHEN 1 THEN 1 ELSE 0 END as exhibition, ENTRYTIME as seed, CASE WHEN RESULTSTATUS = 2 THEN 'DQ' ELSE SWIMTIME END as result FROM CLUB, ATHLETE, SWIMRESULT, SWIMEVENT WHERE SWIMEVENT.SWIMEVENTID = SWIMRESULT.SWIMEVENTID AND ATHLETE.CLUBID = CLUB.CLUBID AND ATHLETE.ATHLETEID = SWIMRESULT.ATHLETEID AND HEATID > 0 AND RESULTSTATUS < 3");
		while(rs.next())
		{
			Result r = new Result(
					rs.getInt("SWIMRESULTID"),
					rs.getInt("event_id"),
					rs.getString("type"),
					rs.getInt("ath_no"),
					rs.getInt("team_no"),
					parseTime(rs.getInt("seed")),
					getResult(rs.getString("result")),
						rs.getInt("exhibition")
					);
			r.setSplits(new ArrayList<Double>());
			results.add(r);

		}

		populateResultsWithSplits(results);

		return results;
	}

	public void populateResultsWithSplits(List<Result> results) throws SQLException {

		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(String.format("SELECT SWIMRESULTID, SWIMTIME FROM SPLIT WHERE SWIMRESULTID NOT IN (SELECT SWIMRESULTID FROM SPLIT GROUP BY SWIMRESULTID HAVING COUNT(SWIMRESULTID) = 1) ORDER BY SWIMRESULTID, DISTANCE"));
		while(rs.next())
		{
			int id = rs.getInt("SWIMRESULTID");
			for (Result result : results) {
				if (result.getId() == id) {
					result.getSplits().add(rs.getInt("SWIMTIME") / 1000.0d);
					break;
				}
			}
		}
	}

	public void populateRelaysWithSplits(List<Relay> relays) throws SQLException {

		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(String.format("SELECT RELAYID, SWIMTIME FROM RELAYSPLIT ORDER BY RELAYID, DISTANCE"));
		while(rs.next())
		{
			int id = rs.getInt("RELAYID");
			for (Relay relay : relays) {
				if (relay.getRelay_no() == id) {
					relay.getSplits().add(rs.getInt("SWIMTIME") / 1000.0d);
					break;
				}
			}
		}
	}

	public List<Double> loadSplits(int result_id) throws SQLException {

		List<Double> splits = new ArrayList<Double>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(String.format("SELECT SWIMTIME as time FROM SPLIT WHERE SWIMRESULTID = %d ORDER BY DISTANCE", result_id));
		while(rs.next())
		{
			splits.add(rs.getInt("time") / 1000.0d);
		}
		return splits;
	}

	@Override
	public List<Relay> loadRelays() throws SQLException {
		List<Relay> relays = new ArrayList<Relay>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT RELAY.SWIMEVENTID as event_id, RELAY.RELAYID as relay_no, GROUP_CONCAT(RELAYPOSITION.RELAYNUMBER || '=' || RELAYPOSITION.ATHLETEID) as athlete_list, RELAY.CLUBID as team_no, RELAY.TEAMNUMBER as relay_team, CASE ROUND WHEN 1 THEN 'F' WHEN 9 THEN 'F' WHEN 2 THEN 'P' WHEN 3 THEN 'SO' END as event_type, CASE RELAY.RESULTSTATUS WHEN 1 THEN 1 ELSE 0 END as exhibition,  ENTRYTIME as seed, CASE WHEN RELAY.RESULTSTATUS = 2 THEN 'DQ' ELSE SWIMTIME END as result FROM CLUB, RELAY, SWIMEVENT, RELAYPOSITION WHERE RELAY.CLUBID = CLUB.CLUBID AND RELAY.SWIMEVENTID = SWIMEVENT.SWIMEVENTID AND RELAY.RELAYID = RELAYPOSITION.RELAYID AND RELAY.RESULTSTATUS < 3 AND HEATID > 0 GROUP BY RELAY.RELAYID, event_id, CLUB.CLUBID ORDER BY event_id, CLUB.CLUBID");
		while(rs.next())
		{
			Relay r = new Relay(rs.getInt("event_id"), 
					rs.getString("event_type"), 
					rs.getInt("team_no"), 
					rs.getString("relay_team"), 
					rs.getString("athlete_list"),
					parseTime(rs.getInt("seed")),
					getResult(rs.getString("result")),
						rs.getInt("exhibition"),
						rs.getInt("relay_no")
					);

			String [] map = r.getAthlete_list().split(",");
			for (String s : map) {
				String [] splits = s.split("=");
				int nr = Integer.parseInt(splits[0]);
				int ath_no = Integer.parseInt(splits[1]);
				switch (nr) {
				case 1:
					r.setAth_no1(ath_no);
					break;
				case 2:
					r.setAth_no2(ath_no);
					break;
				case 3:
					r.setAth_no3(ath_no);
					break;
				case 4:
					r.setAth_no4(ath_no);
					break;

				default:
					break;
				}
			}
			r.setAthlete_list(null);
			r.setSplits(new ArrayList<Double>());
			relays.add(r);
		}

		populateRelaysWithSplits(relays);

		return relays;
	}
	
	private String getResult(String src) {
		if ("DQ".equals(src)) {
			return "DQ";
		} else {
			return String.valueOf(parseTime(Integer.valueOf(src)));
		}
	}
	
	private double parseTime(int seconds) {
		double result = seconds / 1000.0d;
		if (result > 3600) {
			return 0.0;
		} else {
			return result;
		}
	}

}
